package com.fh.service.system.excel;

import com.fh.model.system.ExcelRecord;
import org.apache.commons.io.output.ByteArrayOutputStream;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

/**
 * Created by xxr on 2017/5/13.
 */
@Service
public class ExcelService {

    // 可以捕获内存缓冲区的数据，转换成字节数组
    private ByteArrayOutputStream out = null;

    private HttpServletResponse response;

    private ExcelRecord excelRecord;

    public HttpServletResponse getResponse() {
        return response;
    }

    public void setResponse(HttpServletResponse response) {
        this.response = response;
    }

    public ExcelRecord getExcelRecord() {
        return excelRecord;
    }

    public void setExcelRecord(ExcelRecord excelRecord) {
        this.excelRecord = excelRecord;
    }

    public ByteArrayOutputStream getOut() {
        return out;
    }

    public ExcelService() {
        this.out = new ByteArrayOutputStream();
    }

    /**
     * 开始导出
     * @param response
     * @param excelRecord
     * @return
     */
    public boolean createExportExcel(HttpServletResponse response, ExcelRecord excelRecord){
        setResponse(response);
        setExcelRecord(excelRecord);
        //创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();
        try {
            //建立新的sheet对象（excel的表单），sheet名称暂与文件名一致
            HSSFSheet sheet = wb.createSheet(getExcelRecord().getFileName());
            //创建sheet的行
            setRowHead(sheet,0,getExcelRecord().getColumnNames());
            createRow(sheet,getExcelRecord());
            wb.write(getOut());
        } catch (IOException e) {
            e.printStackTrace();
            return  false;
        }
        return  true;
    }


    //输出Excel文件

    /**
     * 导出excel中
     */
    public void exporting(){
        byte[] bytes = getOut().toByteArray();
        try {
            getResponse().setContentType("application/x-msdownload");
            getResponse().setContentLength(bytes.length);
            getResponse().setHeader("Content-Disposition", "attachment;filename="
                                + java.net.URLEncoder.encode(getExcelRecord().getFileName()+".xls", "UTF-8"));
            OutputStream outputStream = getResponse().getOutputStream();
            outputStream.write(bytes);
            outputStream.flush();//刷新
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     *
     * @param sheet
     * @param excelRecord
     */
    public void createRow(HSSFSheet sheet,ExcelRecord excelRecord){
        List<Map<String,String>> data = excelRecord.getData();
        String[] keys = excelRecord.getKeys();
        for (int i = 1; i < excelRecord.getData().size(); i++) {
            // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
            // 创建一行，在页sheet上
            Row row1 = sheet.createRow(i);
            // 在row行上创建一个方格
            for(int j = 0; j < excelRecord.getKeys().length; j++){
                Cell cell = row1.createCell(j);
                cell.setCellValue(data.get(i).get(keys[j]) == null?" ": data.get(i).get(keys[j]).toString());
            }
        }
    }

    /**
     * 创建表头
     * @param sheet
     * @param index
     * @param columnNames
     */
    public void setRowHead(HSSFSheet sheet,int index,String[] columnNames){
        //在sheet里创建第二行
        HSSFRow row2=sheet.createRow(index);
        //创建单元格并设置单元格内容
        for(int i = 0; i <columnNames.length; i++){
            row2.createCell(i).setCellValue(columnNames[i]);
        }
    }

    /**
     * 创建第一行，并合并单元格
     * @param sheet
     */
     public void setFirstRow(HSSFSheet sheet){
         HSSFRow row1=sheet.createRow(0);
         //创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
         HSSFCell cell=row1.createCell(0);
         //设置单元格内容
         cell.setCellValue("学员考试成绩一览表");
         //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
         sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
     }


}
